package com.skill.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 基础数据库类
 *
 */
public class BaseDB {
	//主机地址IP
	final String HOST = "127.0.0.1";
	//数据库名
	final String DBNAME = "shopdb";
	//mysql的用户名
	final String USER = "root";
	//mysql的安装密码
	final String PASS = "123456";

	private final String url = "jdbc:mysql://" + HOST + ":3306/" + DBNAME
			+ "?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private Connection conn = null;
	// 在查询、更新方法都要使用
	private Statement state = null;
	private ResultSet rs = null;

	/**
	 * 根据数据库的SQL语句查询对应的数据集合并返回。
	 * 
	 * @param sql
	 *            SELECT * FROM .....
	 * @return List
	 */
	public List findData(String sql) {
		//连接数据库
		this.loadDatabase();
		List list = new ArrayList();
		try {
			rs = state.executeQuery(sql);
			while (rs.next()) {
				ResultSetMetaData mdata = rs.getMetaData();
				int count = mdata.getColumnCount();// 列的数量
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= count; i++) {
					String label = mdata.getColumnLabel(i);
					Object obj = rs.getObject(i);
					map.put(label.toLowerCase(), obj);
				}
				list.add(map);
			}
		} catch (SQLException e) {
			System.out.println("执行SQL语句错误：" + e.getMessage());
		} finally {
			closeConnection();
		}
		return list;
	}

	/**
	 * 根据SQL语句更改对应的内容（删除、修改、增加）
	 * 
	 * @param sql
	 * @return 返回影响的行数
	 */
	public int updateData(String sql) {
		//连接数据库
		this.loadDatabase();
		try {
			int result = state.executeUpdate(sql);
			return result;
		} catch (SQLException e) {
			System.out.println("执行SQL语句错误：" + e.getMessage());
		} finally {
			closeConnection();
		}
		return 0;
	}

	/**
	 * 用于连接打开数据库
	 * 
	 * @return
	 */
	public Connection loadDatabase() {

		try {
			//1.加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println(e.getMessage());
			// e.printStackTrace();
		}

		try {
			//2.进行连接
			conn = DriverManager.getConnection(url, USER, PASS);
			state = conn.createStatement();
			System.out.println("数据库连接成功");
		} catch (SQLException e) {
			System.out.println("连接数据库异常" + e.getMessage());
		}

		return conn;
	}

	/**
	 * 关闭连接
	 * 
	 * @return
	 */
	private void closeConnection() {

		try {
			if (state != null){
				state.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (rs != null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public void User(){
		
		
	}

	/*
	 * 练习： 将一个键值对转成对象类型
	 * 例如： 
	 
	 * 现有： 
	 * map.put("id",1);
	 * map.put("name","赵子龙");
	 * map.put("age",24);
	 * map.put("sex","男");
	 * 
	 * 请尝试将键值对转成 User对象类型
	 */

}
